package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.hngm.bean.Message;
import com.hngm.bean.Product;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

public class MessageDao {
	public int insertMsg(Message m) {
		// 定义插入的sql语句
		String sql = String.format("insert into message(name,phone,email,content) values('%s','%s','%s','%s')",
				m.getName(), m.getPhone(), m.getEmail(), m.getContent());
		return DBManager.executeUpdate(sql);
	}

	public ArrayList<Message> queryMessageListByParm(String messagename, String begintime, String endtime, String del,
			String orderby, int beginIndex, int limit) {
		ArrayList<Message> mList = new ArrayList<Message>();
		// 定义sql语句
		// String sql = String.format("select * from product limit %d , %d",
		// beginIndex,limit);
		// 将传过来的查询条件作为where条件：拼接到sql语句中
		StringBuffer sb = new StringBuffer("select * from message where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(messagename)) {
			// sb.append(String.format(" and name like '%s%s%s'",
			// "%",productname,"%"));
			sb.append(String.format(" and name like '%s'", "%" + messagename + "%"));
		}
		if (StringUtil.isNotNullorEmpty(begintime)) {
			sb.append(String.format(" and createtime > '%s'", begintime));
		}
		if (StringUtil.isNotNullorEmpty(endtime)) {
			sb.append(String.format(" and updatetime < '%s'", endtime));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		if (StringUtil.isNotNullorEmpty(orderby)) {
			if ("timeasc".equals(orderby)) {
				sb.append(" order by createtime asc ");
			} else if ("timedesc".equals(orderby)) {
				sb.append(" order by createtime desc ");
			}
		}
		sb.append(String.format(" limit %d,%d ", beginIndex, limit));
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				String email = rs.getString("email");
				String content = rs.getString("content");
				String createtime = rs.getString("createtime");
				String updatetime = rs.getString("updatetime");
				Message m = new Message(id, name, phone, email, content, createtime, updatetime);
				mList.add(m);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return mList;
	}

	public int queyrMessageListByParamCount(String messagename, String begintime, String endtime, String del,
			String orderby) {
		// 定义sql语句
		StringBuffer sb = new StringBuffer("select count(1) as count from message where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(messagename)) {
			// sb.append(String.format(" and name like '%s%s%s'",
			// "%",productname,"%"));
			sb.append(String.format(" and name like '%s'", "%" + messagename + "%"));
		}
		if (StringUtil.isNotNullorEmpty(begintime)) {
			sb.append(String.format(" and createtime > '%s'", begintime));
		}
		if (StringUtil.isNotNullorEmpty(endtime)) {
			sb.append(String.format(" and updatetime < '%s'", endtime));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}

	public static int insertMessage(Message p) {
		// 定义插入sql语句
		String sql = String.format("insert into message(name,phone,email,content) values ('%s','%s','%s','%s')", p.getName(),p.getPhone(),p.getEmail(),p.getContent());
		return DBManager.executeUpdate(sql);
	}

	public int delMessage(String messageId) {
		//定义删除产品的sql语句
		String sql = "delete from message where id = " + messageId;
		return DBManager.executeUpdate(sql);
	}

	public static int edittMessage(Message p) {
		String sql = String.format("update message set "
				+ "name='%s',"
				+ "phone='%s',"
				+ "email='%s',"
				+ "content='%s' "
				+ "where id = %d",
				p.getName(),
				p.getPhone(),
				p.getEmail(),
				p.getContent(),
				p.getId());
		return DBManager.executeUpdate(sql);
	}
}
